-- @owner: wang_zhengyuan
-- @date: 2022/02/11
-- @testpoint:  delete使用子查询，子查询使用组合索引in条件

--step1:清空表中数据expect:成功
truncate table table_idx_in_001;
 
commit;
 
--step2:创建sequence;expect:成功
drop sequence if exists v_seq;
 
create sequence v_seq increment by 1 start with 1;
 
--step3:插入数据;expect:成功
insert into table_idx_in_001 values(1 ,v_seq.nextval,4041.04329387, 'english','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(2 ,v_seq.nextval,4005.70237548, 'painting','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',50);
 
insert into table_idx_in_001 values(3 ,v_seq.nextval,4030.1242131 , 'drawing','doctor',null,'2018-06-27 12:00:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_001 values(4 ,v_seq.nextval,4001.87754238 , null,'master','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(5 ,v_seq.nextval,4046.25584633 , 'system','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',89);
 
insert into table_idx_in_001 values(6 ,v_seq.nextval,4003.68558645 , 'language','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_001 values(7 ,v_seq.nextval,4044.9145853 , 'grammer','doctor','2017-06-15 12:00:00',null,'2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(8 ,v_seq.nextval,4035.26588922 , 'chinese','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',88);
 
insert into table_idx_in_001 values(9 ,v_seq.nextval,4011.51183805 , 'math','doctor','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_001 values(10,v_seq.nextval,4018.35133361 , 'sql majorization','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(11,v_seq.nextval,4041.62109974 , 'big data','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',75);
 
insert into table_idx_in_001 values(12,v_seq.nextval,4018.19233257 , 'performance turning','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00',null,95);
 
insert into table_idx_in_001 values(1 ,v_seq.nextval,4041.04329387, 'english','doctor','2017-06-15 12:00:00','2013-06-10 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(2 ,v_seq.nextval,4041.04329387, 'painting','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',50);
 
insert into table_idx_in_001 values(3 ,v_seq.nextval,4042.04329387, 'drawing','doctor',null,'2018-06-27 12:00:00','2018-06-29 12:00:00',null);
 
insert into table_idx_in_001 values(4 ,v_seq.nextval,4041.04322387, null,'master','2017-06-15 12:00:00',null,'2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(5 ,v_seq.nextval,4041.04329387, 'system','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',89);
 
insert into table_idx_in_001 values(6 ,v_seq.nextval,4041.04322387, 'language','scholar','2018-06-25 12:00:00','2018-06-27 12:23:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_001 values(7 ,v_seq.nextval,4041.04339387, 'grammer','doctor','2017-06-15 12:00:00',null,'2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(8 ,v_seq.nextval,4041.04329387, 'chinese','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
 
insert into table_idx_in_001 values(9 ,v_seq.nextval,4041.14329387, 'math',' ','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_001 values(10,v_seq.nextval,4045.04329387, null,'doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(11,v_seq.nextval,4041.04329489387, 'big data','master','2018-06-15 12:00:00',null,'2018-06-25 12:00:00',75);
 
insert into table_idx_in_001 values(12,v_seq.nextval,4041.0432459387, 'performance turning','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00',null,95);
 
commit;
 
begin
    for i in 1..300 loop
    insert into table_idx_in_001 values(mod(i,13),v_seq.nextval,v_seq.nextval/i,'big data','doctor','2018-06-15 12:00:00',
    add_months('2018-06-29 12:00:00',mod(i,6)),'2017-06-25 12:00:00',80+mod(i,15));
    end loop;
end;
/
 
commit;
 
begin
    for i in 1..300 loop
    insert into table_idx_in_001 values(mod(i,11),v_seq.nextval,v_seq.nextval/i,'performance turning','scholar',
    '2018-06-15 12:00:00','2017-06-25 12:00:00',add_months('2018-06-29 12:00:00',trunc(i/6)),50+i);
    end loop;
end;
/
 
commit;
 
begin
    for i in 1..300 loop
    insert into table_idx_in_001 values(mod(i,8),v_seq.nextval,v_seq.nextval/i-5,'sql majorization','master',
    add_months('2018-06-29 12:00:00',mod(i,6)),'2018-06-15 12:00:00',add_months('2017-06-25 12:00:00',mod(i,3)),90-mod(i,15));
    end loop;
end;
/
 
commit;
 
--step4:delete使用子查询，子查询使用组合索引in条件;expect:成功
delete from table_idx_in_001 where course_id in (
    select staff_id from table_idx_in_school_002 where higest_degree
    in ('master','scholar','ungrduated','unknown') and t_sql_note in ('211&985','985','unknown'));
 
commit;
 
--step5:查询数据;expect:打印数据成功
select staff_id,course_id from table_idx_in_001 order by 1,2;

--step6:环境清理;expect:成功
drop sequence if exists v_seq;
 
